/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.gidoo.xmldb;

import java.io.Reader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.stream.XMLInputFactory;
import javax.xml.stream.XMLStreamException;
import javax.xml.stream.XMLStreamReader;

/**
 * This class provides the interface to manage the documents of a gidooXMLDB database.
 * @author Thomas Krause
 */
public class GidooXMLDBManager
{

  private static Logger logger = Logger.getLogger(GidooXMLDBManager.class.getName());

  /**
   * Imports an XML document into the database.
   * @param databaseName The database to operate on.
   * @param docName The new internal document name.
   * @param xmlSource The xml content.
   * @param flushCounter The count of parser events after which the import should be flushed.
   *                     -1 if it should not be set
   * @throws javax.xml.stream.XMLStreamException
   */
  public static void importDocument(String databaseName, String docName,
    Reader xmlSource, long flushCounter) throws XMLStreamException
  {

    XMLInputFactory factory = XMLInputFactory.newInstance();
    XMLStreamReader parser = factory.createXMLStreamReader(xmlSource);
    Connection con = ConnectionHelper.connect2DB(databaseName, false);

    if(con != null)
    {
      try
      {

        Statement stmt = con.createStatement();
        // insert document entry
        con.setAutoCommit(false);

        try
        {
          if(stmt.executeUpdate("insert into documents(name) " +
            "values('" + docName + "')", Statement.RETURN_GENERATED_KEYS) > 0)
          {
            ResultSet s = stmt.getGeneratedKeys();
            if(s.next())
            {
              long docID = s.getLong(1);

              logger.info("inserted new document with id " + docID +
                " and started parsing");

              // parse
              Parser p = new Parser(con, parser, docID);
              if(flushCounter > 0)
              {
                p.setEventCountForFlush(flushCounter);
              }
              p.parse();
            }
          }
          // *now* we can commit
          logger.info("commiting");
          con.commit();
          logger.info("finished commit");
        }
        catch(SQLException ex)
        {
          logger.log(Level.SEVERE, null, ex);

          logger.severe("rollback of transaction");
          con.rollback();
        }
        finally
        {
          con.setAutoCommit(true);
        }

        vacuum(stmt);

      }
      catch(SQLException ex)
      {
        logger.log(Level.SEVERE, null, ex);
      }
    }


  }

  /**
   * Deletes an existing document from a database.
   * @param databaseName
   * @param docName
   */
  public static void deleteDocument(String databaseName, String docName)
  {
    logger.info("trying to delete " + docName + " from " + databaseName);
    Connection con = ConnectionHelper.connect2DB(databaseName, false);
    if(con != null)
    {
      try
      {
        PreparedStatement psDelete = con.prepareStatement("delete from documents where name=?");
        psDelete.setString(1, docName);
        if(psDelete.executeUpdate() == 0)
        {
          logger.info("There is no document with that name");
        }
        else
        {
          logger.info("document " + docName + " deleted");
          vacuum(con.createStatement());
        }
      }
      catch(SQLException ex)
      {
        logger.log(Level.SEVERE, null, ex);
      }

    }
  }

  private static void createDBSchema(Connection conn)
  {
    try
    {
      Statement stmt = conn.createStatement();

      stmt.execute(
        "create table documents (" +
        "id bigint generated always as identity, " +
        "name varchar(255) not null, " +
        "primary key(id), " +
        "unique (name))");

      stmt.execute(
        "create table contents (" +
        "id bigint generated by default as identity, " +
        "docID bigint, " +
        "primary key(id), " +
        "foreign key(docID) references documents(id) on delete cascade)");

      stmt.execute(
        "create table elements (" +
        "id bigint, " +
        "name varchar(50) not null, " +
        "ns varchar(50) not null, " +
        "primary key(id), " +
        "foreign key(id) references contents(id)  on delete cascade)");
      stmt.execute("create index elementsIndex on elements(name, ns)");

      stmt.execute(
        "create table characters (\n" +
        "id bigint,\n" +
        "value varchar(32672) not null,\n" +
        "value_lower generated always as (lower(value)), \n" +
        "foreign key(id) references contents(id)  on delete cascade \n" +
        ")");
      stmt.execute("create index charactersValueIndex on characters(value)");
      stmt.execute("create index charactersValueLowerIndex on characters(value_lower)");

      stmt.execute(
        "create table attributes (" +
        "id bigint generated always as identity, " +
        "elementID bigint not null, " +
        "name varchar(50) not null, " +
        "ns varchar(50) not null, " +
        "value varchar(300) not null, " +
        "value_lower generated always as (lower(value)), " +
        "primary key(id), " +
        "foreign key(elementID) references elements(id) on delete cascade , " +
        "unique(elementID, name, ns)" +
        ")");

      stmt.execute("create index attributesIndex on attributes(name, ns, value_lower)");
      stmt.execute("create index attributesValueOnlyIndex on attributes(value)");
      stmt.execute("create index attributesValueLowerOnlyIndex on attributes(value_lower)");

      stmt.execute(
        "create table children (" +
        "idParent bigint, " +
        "idChild bigint, " +
        "orderVal bigint not null, " +
        "primary key(idParent, idChild), " +
        "unique (idParent, orderVal), " +
        // TODO: check if this might be dangerous in the sense of an invalid structure of the document
        "foreign key (idParent) references contents(id) on delete cascade, " +
        "foreign key (idChild) references contents(id) on delete cascade) ");

      stmt.execute(
        "create table uri_for_ns (" +
        "docID bigint, " +
        "uri varchar(255) not null, " +
        "prefix varchar(50) not null, " +
        "primary key(prefix, docID), " +
        "unique (uri, docID), " +
        "foreign key (docID) references documents(id) on delete cascade )");

      // stored functions

      stmt.execute("CREATE FUNCTION PREDEC \n" +
        "(elementID bigint) \n" +
        "RETURNS TABLE (" +
        "id BIGINT, " +
        "leveldist integer) \n" +
        "language java " +
        "parameter style DERBY_JDBC_RESULT_SET " +
        "reads sql data " +
        "external name 'org.gidoo.xmldb.stored.Predecessors.predec'");

      stmt.execute("CREATE FUNCTION PREDECBYNAME \n" +
        "(elementID bigint, name varchar(50)) \n" +
        "RETURNS bigint \n" +
        "language java " +
        "parameter style JAVA " +
        "reads sql data " +
        "external name 'org.gidoo.xmldb.stored.Predecessors.predecByName'");

    }
    catch(SQLException ex)
    {
      Logger.getLogger(GidooXMLDBManager.class.getName()).log(Level.SEVERE, null, ex);
    }
  }

  private static void vacuum(Statement stmt) throws SQLException
  {

    logger.info("compressing tables and indexes");
    stmt.execute("call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'DOCUMENTS', 1)");
    stmt.execute("call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'URI_FOR_NS', 1)");
    stmt.execute("call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'CONTENTS', 1)");
    stmt.execute("call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ELEMENTS', 1)");
    stmt.execute("call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ATTRIBUTES', 1)");
    stmt.execute("call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'CHARACTERS', 1)");
    stmt.execute("call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'CHILDREN', 1)");
    logger.info("finished compression");
  }

  private static void dropSchema(Connection con)
  {
    try
    {
      Statement stmt = con.createStatement();

      try
      {
        stmt.execute("drop table children");
      }
      catch(SQLException ex)
      {
      }

      try
      {
        stmt.execute("drop table attributes");
      }
      catch(SQLException ex)
      {
      }

      try
      {
        stmt.execute("drop table elements");
      }
      catch(SQLException ex)
      {
      }

      try
      {
        stmt.execute("drop table characters");
      }
      catch(SQLException ex)
      {
      }


      try
      {
        stmt.execute("drop table contents");
      }
      catch(SQLException ex)
      {
      }

      try
      {
        stmt.execute("drop table uri_for_ns");
      }
      catch(SQLException ex)
      {
      }

      try
      {
        stmt.execute("drop table documents");
      }
      catch(SQLException ex)
      {
      }

      // stored procedures
      try
      {
        stmt.execute("drop function PREDEC");
      }
      catch(SQLException ex)
      {
      }
      try
      {
        stmt.execute("drop function PREDECBYNAME");
      }
      catch(SQLException ex)
      {
      }


    }
    catch(SQLException ex)
    {
    }
  }

  public static void initDB(String databaseName)
  {
    Connection c = ConnectionHelper.connect2DB(databaseName, true);
    try
    {
      Statement stmt = c.createStatement();
      stmt.execute("select * from documents");

      // need to delete
      dropSchema(c);

    }
    catch(SQLException ex)
    {
      // ignore
    }

    // finally recreate
    createDBSchema(c);

  }
}